﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace TestJQGrid
{
    public partial class SubRules : System.Web.UI.Page
    {
        #region Global declarations

        #endregion

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                fill_fields();
                fill_Formulas();
                fill_Slabs();
                fill_dlSubrules();

                
                btnSave.Visible = false;
                ViewState["SubruleIDToUpdate"] = 0;

                lblMenuName.Text = "SUB RULE";
                lblMenuName.Font.Bold = true;
                lblMenuName.Font.Size = 14;
                lblMenuName.ForeColor = System.Drawing.Color.Navy;
            }

            lblWhenExp.Text = "";
            lblThenExp.Text = "";

            ddReturnType.Enabled = false;
        }

        private void fill_fields()
        {
            listFields.DataSource = GlobalMethods.ExecuteSelectQuery("select (cp.TableName + '.' + cp.ColumnName) AS ctxt,('@('+cp.TableName + '.' + cp.ColumnName+')') AS cval  from ColumnProperties cp where cp.TableName in ( select tr.RelatedTable from TableRelation tr, ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "' AND pl.TableName = tr.PrimaryTable ) OR cp.TableName in ( select pl.TableName from ProductList pl where pl.ProductName = '" + Session["ProductName"].ToString() + "') AND cp.ColumnName not in ('EntryStatus','Product','TransId','ProductName') Order By ctxt");
            listFields.DataTextField = "ctxt";
            listFields.DataValueField = "cval";
            listFields.DataBind();
        }

        private void fill_Formulas()
        {
            listFormula.DataSource = GlobalMethods.ExecuteSelectQuery("select FormulaName as ctxt,'$'+FormulaName+'()' as cval from FormulaTable where ProductName='" + Session["ProductName"].ToString() + "' and FormulaType='Formula' and SubType in ('SubRule','Slab Structure') order by FormulaName");
            listFormula.DataTextField = "ctxt";
            listFormula.DataValueField = "cval";
            listFormula.DataBind();
        }

        private void fill_Slabs()
        {
            listSlabs.DataSource = GlobalMethods.ExecuteSelectQuery("select SlabName as ctxt,'@('+SlabName+')' as cval from Slabs s where s.ProductName = '" + Session["ProductName"].ToString() + "' AND s.todate > '" + DateTime.Now.ToString() + "' order by SlabName");
            listSlabs.DataTextField = "ctxt";
            listSlabs.DataValueField = "cval";
            listSlabs.DataBind();
        }

        private void fill_dlSubrules()
        {
            dlsubrule.DataSource = GlobalMethods.ExecuteSelectQuery("select FormulaName,ID from FormulaTable where FormulaType='Subrule' order by FormulaName");
            dlsubrule.DataBind();
        }

        protected void btnValidate_Click(object sender, EventArgs e)
        {
            string result = "";
            string when_expResult = "", when_expResultType = "";
            string then_expResult = "", then_expResultType = "";
            string when_expression = txtWhenPart.Text.Trim().Replace(" ", "");
            string then_expression = txtThenPart.Text.Trim().Replace(" ", "");

            if (when_expression.Length > 0)
            {
                object obj = ExpressionEvaluator.isValidExp(when_expression);
                when_expResult = Convert.ToString(obj);
                when_expResultType = obj.GetType().ToString();
            }

            if (then_expression.Length > 0)
            {
                object obj = ExpressionEvaluator.isValidExp(then_expression);
                then_expResult = Convert.ToString(obj);
                then_expResultType = obj.GetType().ToString();
            }

            if (when_expResult != "" && when_expResult.ToUpper() != "TRUE" && when_expResult.ToUpper() != "FALSE") //when_expResult.IndexOf("Invalid") > -1
            {
                if (string.IsNullOrEmpty(result))
                {
                    result = " WHEN expression";
                }
                else
                {
                    result += " and WHEN expression";
                }

                lblWhenExp.Text = "Invalid expression";
                lblWhenExp.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                if (when_expResult != "")
                {
                    lblWhenExp.Text = "Valid expression";
                    lblWhenExp.ForeColor = System.Drawing.Color.Green;
                }
            }

            if (then_expResult.IndexOf("Invalid") > -1)
            {
                if (string.IsNullOrEmpty(result))
                {
                    result = " Then expression";
                }
                else
                {
                    result += " and Then expression";
                }

                lblThenExp.Text = "Invalid expression";
                lblThenExp.ForeColor = System.Drawing.Color.Red;
            }
            else
            {
                lblThenExp.Text = "Valid expression";
                lblThenExp.ForeColor = System.Drawing.Color.Green;
            }

            if (result != "")
            {
                btnSave.Visible = false;
            }
            else
            {
                btnSave.Visible = true;
            }
        }

        protected void lbt_formula_Click(object sender, EventArgs e)
        {
            int rid = 0;
            LinkButton lbtn = sender as LinkButton;

            if (lbtn != null)
            {
                rid = Convert.ToInt32(lbtn.CommandArgument);
                showFormulDetails(rid);
                ViewState["SubruleIDToUpdate"] = rid;
            }
        }

        private void showFormulDetails(int fid)
        {
            DataTable dt = new DataTable();
            dt = GlobalMethods.ExecuteSelectQuery("select * from FormulaTable ft where ft.ID=" + fid);

            if (dt != null && dt.Rows.Count > 0)
            {
                txtSubruleName.Text = dt.Rows[0]["FormulaName"].ToString();
                ddReturnType.SelectedValue = dt.Rows[0]["ReturnType"].ToString();
                txtWhenPart.Text = dt.Rows[0]["WhenPart"].ToString();
                txtThenPart.Text = dt.Rows[0]["ThenPart"].ToString();

                txtFromDate.Text = Convert.ToDateTime(dt.Rows[0]["FromDate"].ToString()).ToString("dd-MMM-yyyy"); 
                txtToDate.Text = Convert.ToDateTime(dt.Rows[0]["ToDate"].ToString()).ToString("dd-MMM-yyyy");
            }
        }

        protected void btnReset_Click(object sender, EventArgs e)
        {
            resetForm();
        }

        private void resetForm()
        {
            txtSubruleName.Text = "";
            ddReturnType.SelectedValue = "number";
            txtThenPart.Text = "";
            txtWhenPart.Text = "";
            lblThenExp.Text = "";
            lblWhenExp.Text = "";

            txtFromDate.Text = "";
            txtToDate.Text = "";

            ViewState["SubruleIDToUpdate"] = 0;
            btnSave.Visible = false;
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            if (Convert.ToDateTime(txtFromDate.Text) < Convert.ToDateTime(txtToDate.Text))
            {
                DataTable tblsrc = new DataTable();

                string strGroup = "";
                string strWhere = "";
                string tableNames = "";
                string expQuery = txtThenPart.Text.Trim().Replace(" ", "");

                tblsrc = GlobalMethods.ExecuteSelectQuery("select distinct t.PrimaryTable from TableRelation t");

                if (tblsrc != null && tblsrc.Rows.Count > 0)
                {
                    foreach (DataRow dr in tblsrc.Rows)
                    {
                        if (string.IsNullOrEmpty(tableNames))
                        {
                            tableNames = dr["PrimaryTable"].ToString();
                        }
                        else
                        {
                            tableNames += "," + dr["PrimaryTable"].ToString();
                        }
                    }
                }

                // Group By Query
                if (expQuery.IndexOf("SUM") > -1 || expQuery.IndexOf("MIN") > -1 || expQuery.IndexOf("MAX") > -1 || expQuery.IndexOf("AVG") > -1 || expQuery.IndexOf("COUNT") > -1)
                {
                    strGroup = txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                    strGroup = strGroup.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");

                    strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                    strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");


                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;
                    expQuery += " group by " + strGroup;
                    //expQuery += " having " + txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");

                }
                else // Without GROUP BY clause
                {
                    strWhere = txtWhenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "");
                    strWhere = strWhere.Replace("SUM", "").Replace("MIN", "").Replace("MAX", "").Replace("AVG", "").Replace("COUNT", "");


                    expQuery = "select " + txtThenPart.Text.Trim().Replace(" ", "").Replace("@", "").Replace("$", "") + " ";
                    expQuery += " from " + tableNames;
                    if (strWhere != "")
                        expQuery += " where " + strWhere;

                }

                if (Convert.ToInt32(ViewState["SubruleIDToUpdate"]) != 0)
                {
                    if (GlobalMethods.ExecuteNonQuery("update FormulaTable set FormulaName='" + txtSubruleName.Text.Trim() + "',WhenPart='" + txtWhenPart.Text.Trim().Replace(" ", "") + "',ThenPart='" + txtThenPart.Text.Trim().Replace(" ", "") + "',ReturnType='" + ddReturnType.SelectedValue.ToString() + "',FinalQuery='" + expQuery.Trim() + "',FromDate='" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "',ToDate='" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "' where ID=" + Convert.ToInt32(ViewState["SubruleIDToUpdate"])) > 0)
                    {
                        fill_dlSubrules();
                        WebMsgBox.Show("Subrule updated successfully.");
                    }
                }
                else
                {
                    GlobalMethods.ExecuteNonQuery("insert into FormulaTable values('" + txtSubruleName.Text.Trim().Replace("'", "") + "','" + txtWhenPart.Text.Trim() + "','" + txtThenPart.Text.Trim() + "','" + ddReturnType.SelectedValue.ToString() + "','Subrule','" + DateTime.Now.ToString() + "','" + Session["loginId"].ToString() + "','" + expQuery + "','" + Session["ProductName"].ToString() + "','" + Convert.ToDateTime(txtFromDate.Text).ToString("yyyy-MM-dd") + "','" + Convert.ToDateTime(txtToDate.Text).ToString("yyyy-MM-dd") + "','','')");
                    fill_dlSubrules();
                    WebMsgBox.Show("Subrule Saved Successfully");
                }
            }
            else
            {
                WebMsgBox.Show("From date is less then To date.");
            }
        }
    }
}